﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KandieWebProjectI.DAL.Helper;
using KandieWebProjectI.Domain.Model;
using System.Data;
using System.Data.SqlClient;

namespace KandieWebProjectI.DAL.Dal
{
    public class ClienteDao : IDal<Cliente>
    {
        private SqlConnection sConnection;
        private SqlCommand sCommand;
        private SqlDataReader sDataReader;
        private bool statusError = false;
        private IList<Cliente> Clientes;
        private ConnectionHelper conHelper;

        public ClienteDao()
        {
            this.sConnection = new SqlConnection();
            this.sCommand = new SqlCommand();
            this.Clientes = new List<Cliente>();
            this.conHelper = new ConnectionHelper();
        }

        #region Implementation of IDal<Cliente>

        public bool Gravar(Cliente entity)
        {
            StringBuilder querySql = new StringBuilder();

            conHelper.OpenConnection(sConnection);
            sCommand.Connection = sConnection;

            querySql.Append("INSERT INTO Cliente (Nome, Aniversario, DataCadastro) ");
            querySql.Append("VALUES( @Nome, @Aniversario, @DataCadastro");

            sCommand.CommandText = querySql.ToString();
            sCommand.Parameters.AddWithValue("@Nome", entity.Nome);
            sCommand.Parameters.AddWithValue("@Aniversario", entity.Aniversario);
            sCommand.Parameters.AddWithValue("@DataCadastro", entity.DataCadastro);

            try
            {
                sCommand.ExecuteNonQuery();
            }
            catch
            {
                statusError = true;
            }

            return statusError;
        }

        public bool Deletar(int _id)
        {
            StringBuilder querySql = new StringBuilder();

            querySql.Append("DELETE FROM Cliente ");
            querySql.Append("WHERE Id = @Id");

            sCommand.CommandText = querySql.ToString();
            sCommand.Parameters.AddWithValue("@Id", _id);

            conHelper.OpenConnection(sConnection);
            sCommand.Connection = sConnection;

            try
            {
                sCommand.ExecuteNonQuery();
            }
            catch
            {
                statusError = true;
            }

            return statusError;
        }

        public bool Atualizar(Cliente entity)
        {
            StringBuilder querySql = new StringBuilder();
            querySql.Append("UPDATE Cliente ");
            querySql.Append("SET Nome = @Nome, " +
                            "Aniversario = @Aniversario, " +
                            "DataCadastro = @DataCadastro " +
                            "WHERE Id = @Id");

            sCommand.CommandText = querySql.ToString();
            sCommand.Parameters.AddWithValue("@Id", entity.Id);
            sCommand.Parameters.AddWithValue("@Nome", entity.Nome);
            sCommand.Parameters.AddWithValue("@Aniversario", entity.Aniversario);
            sCommand.Parameters.AddWithValue("@DataCadastro", entity.DataCadastro);

            conHelper.OpenConnection(sConnection);
            sCommand.Connection = sConnection;

            try
            {
                sCommand.ExecuteNonQuery();
            }
            catch
            {
                statusError = true;
            }

            return statusError;
        }

        public IList<Cliente> RetornaTodos()
        {
            StringBuilder querySql = new StringBuilder();
            querySql.Append("SELECT Id, Nome, Aniversario, DataCadastro FROM Cliente");

            conHelper.OpenConnection(sConnection);
            sCommand.CommandText = querySql.ToString();
            sCommand.Connection = sConnection;

            try
            {
                sDataReader = sCommand.ExecuteReader();

                while (sDataReader.Read()) 
                {
                    Cliente cli = new Cliente();
                    cli.Id = Convert.ToInt32(sDataReader["Id"].ToString());
                    cli.Nome = sDataReader["Nome"].ToString();
                    cli.Aniversario = Convert.ToDateTime(sDataReader["Aniversario"].ToString());
                    cli.DataCadastro = Convert.ToDateTime(sDataReader["DataCadastro"].ToString());
                    this.Clientes.Add(cli);
                }
            }
            finally 
            {
                conHelper.CloseConnection(sConnection);
            }

            return Clientes;
        }

        public Cliente RetornaPorId(int _id)
        {
            StringBuilder querySql = new StringBuilder();
            Cliente cli = new Cliente();

            querySql.Append("SELECT Id, Nome, Aniversario, DataCadastro FROM Cliente WHERE Id = @Id");
            sCommand.Parameters.AddWithValue("@Id", _id);

            conHelper.OpenConnection(sConnection);
            sCommand.CommandText = querySql.ToString();
            sCommand.Connection = sConnection;

            try
            {
                sDataReader = sCommand.ExecuteReader();

                while (sDataReader.Read())
                {
                    cli.Id = Convert.ToInt32(sDataReader["Id"].ToString());
                    cli.Nome = sDataReader["Nome"].ToString();
                    cli.Aniversario = Convert.ToDateTime(sDataReader["Aniversario"].ToString());
                    cli.DataCadastro = Convert.ToDateTime(sDataReader["DataCadastro"].ToString());
                }
            }
            finally
            {
                conHelper.CloseConnection(sConnection);
            }

            return cli;
        }

        #endregion

        private void LiberarObjetos()
        {
            this.sCommand.Dispose();
            this.sConnection.Dispose();
        }
    }
}
